package com.yvan.serverless.jdbc;

import com.google.common.base.Strings;
import com.google.common.collect.Sets;
import com.yvan.Conv;
import com.yvan.platform.JsonWapper;
import com.yvan.serverless.mybatis.DataSourceHelper;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.context.request.RequestContextHolder;
import org.springframework.web.context.request.ServletRequestAttributes;

import javax.servlet.http.HttpServletRequest;
import java.util.List;
import java.util.Map;
import java.util.Set;

@Slf4j
public class Sql {
    public static final String YVANUI_FILTER = "__YV_FILTER__";
    public static final String YVANUI_SORT = "__YV_SORT__";

    static HttpServletRequest currentRequest() {
        return ((ServletRequestAttributes) RequestContextHolder.currentRequestAttributes())
                .getRequest();
    }

    public static String filter(String sql, String field) {
        HttpServletRequest req = currentRequest();
        Map<String, Object> filterModel = (Map<String, Object>) req.getAttribute(YVANUI_FILTER);
        JsonWapper jwFilter = new JsonWapper(filterModel);
        String dbType = DataSourceHelper.getDbType();

        String filterType = jwFilter.asStr(field, "filterType");
        if (Strings.isNullOrEmpty(filterType)) {
            return "";
        }

        switch (filterType) {
            case "text": {
                // filterType: text
                // type: startsWith / equals / contains,
                // filter: "abc"

                String filter = jwFilter.asStr(field, "filter");
                if (Strings.isNullOrEmpty(filter)) {
                    return "";
                }

                String type = jwFilter.asStr(field, "type");
                switch (type) {
                    case "startsWith":
                        if ("oracle".equalsIgnoreCase(dbType)) {
                            return sql + " like '" + replaceParamLike(dbType, filter) + "%'ESCAPE '\\'";
                        }
                        return sql + " like '" + replaceParamLike(dbType, filter) + "%'";

                    case "contains":
                        if ("oracle".equalsIgnoreCase(dbType)) {
                            return sql + " like '%" + replaceParamLike(dbType, filter) + "%'ESCAPE '\\'";
                        }
                        return sql + " like '%" + replaceParamLike(dbType, filter) + "%'";

                    case "equals":
                        return sql + " = '" + replaceParam(dbType, filter) + "'";
                }
                log.error("错误的过滤格式 text." + type);
                return "";
            }

            case "number": {
                // filterType: number
                // filterTo: null (inRange 情况下有值)
                // filter: 1
                // type: equals / notEqual / lessThan / lessThanOrEqual / greaterThan / greaterThanOrEqual / inRange
                if (!jwFilter.contains(field, "filter")) {
                    return "";
                }
                int filter = jwFilter.asInt(field, "filter");
                String type = jwFilter.asStr(field, "type");
                switch (type) {
                    case "equals":
                        return sql + " = " + filter;

                    case "notEqual":
                        return sql + " != " + filter;

                    case "lessThan":
                        return sql + " < " + filter;

                    case "lessThanOrEqual":
                        return sql + " <= " + filter;

                    case "greaterThan":
                        return sql + " > " + filter;

                    case "greaterThanOrEqual":
                        return sql + " >= " + filter;

                    case "inRange":
                        int filterTo = jwFilter.asInt(field, "filterTo");
                        int min = filterTo > filter ? filter : filterTo;
                        int max = filterTo > filter ? filterTo : filter;
                        return sql + " BETWEEN " + min + " AND " + max;
                }
                log.error("错误的过滤格式 number." + type);
                return "";
            }

            case "date": {
                // filterType: date
                // type: inRange
                // dateTo: 2019-10-27
                // dateFrom: 2019-10-08
                String type = jwFilter.asStr(field, "type");
                String dateTo = jwFilter.asStr(field, "dateTo");
                String dateFrom = jwFilter.asStr(field, "dateFrom");
                if (Strings.isNullOrEmpty(dateTo) || Strings.isNullOrEmpty(dateFrom)) {
                    return "";
                }
                switch (type) {
                    case "inRange":
                        if ("oracle".equalsIgnoreCase(dbType)) {
                            int cp = dateTo.compareTo(dateFrom);
                            String min = cp > 0 ? dateFrom : dateTo;
                            String max = cp > 0 ? dateTo : dateFrom;

                            return sql + " BETWEEN to_date('" + replaceParam(dbType, min) +
                                    " 00:00:00','yyyy-mm-dd HH24:MI:SS') AND to_date('" + replaceParam(dbType, max) +
                                    " 23:59:59','yyyy-mm-dd HH24:MI:SS')";
                        }

                        int cp = dateTo.compareTo(dateFrom);
                        String min = cp > 0 ? dateFrom : dateTo;
                        String max = cp > 0 ? dateTo : dateFrom;
                        return sql + " BETWEEN '" +
                                replaceParam(dbType, min) +
                                "' AND '" + replaceParam(dbType, max) + "'";
                }
                log.error("错误的过滤格式 date." + type);
                return "";
            }

            case "set": {
                Object filterRaw = jwFilter.get(field, "filter");
                if (filterRaw instanceof List) {
                    List<Map<String, Object>> filter = (List<Map<String, Object>>) filterRaw;
                    Set<String> ids = Sets.newHashSet();
                    for (Map<String, Object> item : filter) {
                        String id = Conv.NS(item.get("id"));
                        if (!Strings.isNullOrEmpty(id)) {
                            ids.add(id);
                        }
                    }

                    String sp = "";
                    StringBuilder sb = new StringBuilder();
                    for (String id : ids) {
                        sb.append(sp).append("'").append(replaceParam(dbType, id)).append("'");
                        sp = ",";
                    }
                    return sql + " in (" + sb.toString() + ")";
                }
                log.error("错误的过滤格式 set." + Conv.NS(jwFilter.get(field, "filter")));
                return "";
            }

            default:
                log.error("错误的过滤格式 " + filterModel.toString());
        }

        //jwFilter
        //  -> 字段名
        //      filterType: text
        //      type: startsWith / equals / contains,
        //      filter: "abc"
        //
        //      filterType: number
        //      filterTo: null (inRange 情况下有值)
        //      filter: 1
        //      type: equals / notEqual / lessThan / lessThanOrEqual / greaterThan / greaterThanOrEqual / inRange
        //
        //      filterType: date
        //      type: inRange
        //      dateTo: 2019-10-27
        //      dateFrom: 2019-10-08
        //
        //      filterType: set
        //      filter: [{id: "M", text: "男"}]

        return "";
    }

    /**
     * 排序关键字
     * 是否添加 order by 关键字
     */
    public static String sort(boolean addKeyword, String defaultValue) {
        // [
        //   {colId: "gender", sort: "asc"}
        // ]
        HttpServletRequest req = currentRequest();
        List<Map<String, Object>> orderByModel = (List<Map<String, Object>>) req.getAttribute(YVANUI_SORT);

        if (orderByModel == null || orderByModel.size() <= 0) {
            //没有任何排序
            if (Strings.isNullOrEmpty(defaultValue)) {
                //没有默认值
                return "";
            } else {
                //有默认值
                if (addKeyword) {
                    return " order by " + defaultValue;
                } else {
                    return defaultValue;
                }
            }
        }

        //有排序
        StringBuilder sb = new StringBuilder();
        if (addKeyword) {
            sb.append(" order by ");
        }
        String sp = "";
        for (Map<String, Object> item : orderByModel) {
            sb.append(sp).append(item.get("colId")).append(" ").append(item.get("sort"));
            sp = ",";
        }

        if (sb.indexOf("'") >= 0) {
            log.error("orderBy 发现SQL注入:" + sb.toString());
            return "";
        }
        return sb.toString();
    }

    public static String sort(String defaultValue) {
        return sort(true, defaultValue);
    }

    public static String sort() {
        return sort(true, "");
    }

    /**
     * 对 SQL 注入的敏感字符进行转义
     */
    public static String replaceParam(String dbType, String value) {
        //对 SQL 注入的敏感字符进行替换
        if (Strings.isNullOrEmpty(value)) {
            return "";
        }
        if ("oracle".equalsIgnoreCase(dbType)) {
            return value.replace("'", "''");
        }
        return value
                .replace("\0", "\\0")
                .replace("\n", "\\n")
                .replace("\r", "\\r")
                .replace("'", "\\'");
    }

    /**
     * 对 like 查询值中的 _ % 符号进行转义
     */
    public static String replaceParamLike(String dbType, String value) {
        //对 _% 符号进行转移
        return replaceParam(dbType, value)
                .replace("_", "\\_")
                .replace("%", "\\%");
    }
}
